alter table jms_dm.dm_tab_missrate_waybill_detail_dt
    set tblproperties ('external.table.purge' = 'true');
--hive删除表，同步会删除文件；文件会临时存储到回收站
drop table jms_dm.dm_tab_missrate_waybill_detail_dt;
CREATE EXTERNAL TABLE if not exists jms_dm.dm_tab_missrate_waybill_detail_dt
(
    day_time             string comment '日期',
    billcode             string comment '运单号',
    site_code            string comment '转运中心编码',
    site_name            string comment '转运中心名称',
    proxy_area_code      string comment '代理区编码',
    proxy_area_name      string comment '代理区编码名称',
    scantime             string comment '发件扫描时间',
    next_site_code       string comment '发件下一站code',
    next_site            string comment '发件下一站',
    scanuser             string comment '发件扫描员',
    first_code           string comment '一段码',
    second_code          string comment '二段码',
    secondcode_site_id   string comment '运单二段码对应网点id',
    secondcode_site_code string comment '运单二段码对应网点code',
    secondcode_site_name string comment '运单二段码对应网点名称',
    distribution_code    string comment '可派货集散点-时效-code',
    distribution_name    string comment '可派货集散点-时效-名称',
    out_enport_code      string comment '可派货集散点-二段码-code',
    out_enport_name      string comment '可派货集散点-二段码-名称',
    sign_network_id      string comment '时效签收网点id',
    sign_network_code    string comment '时效签收网点code',
    sign_network_name    string comment '时效签收网点名称',
    errorseparate_type   int comment '时效签收网点对应的操作错分类型:1是三段码不全错分,2是三段码错误错分,3是操作错分',
    is_sign              int comment '是否已时效签收,1是0否',
    is_same_line1        int comment '是否在同一支线,时效,,1是0否',
    is_same_line2        int comment '是否在同一支线,二段码,,1是0否',
    sign_cuofen          int comment '1代表签收错分',
    unsign_cuofen        int comment '1代表未签收错分'
)
    COMMENT '操作错分率明细'
    PARTITIONED BY ( dt STRING COMMENT '扫描时间')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_tab_missrate_waybill_detail_dt'
;



alter table jms_dm.dm_tab_missrate_trace_waybill_next_dt
    set tblproperties ('external.table.purge' = 'true');
drop table jms_dm.dm_tab_missrate_trace_waybill_next_dt;
create external table jms_dm.dm_tab_missrate_trace_waybill_next_dt
(
    date_time                        string comment '日期',
    waybill_no                       string comment '运单',
    first_code                       string comment '一段码',
    second_code                      string comment '二段码',
    third_code                       string comment '三段码',
    send_station_network_code        string comment '发件站点',
    send_station_network_type        string comment '发件站点类型',
    send_station_send_later_time     string comment '发件站点时间',
    sent_station_send_packagecode    string comment '发件站点运单对应包号',
    send_station_next_station        string comment '发件站点下一站',
    send_station_next_statation_type string comment '发件站点下一站类型',
    next_station_arrival_early_time  string comment '下一站到件时间',
    deliver_later_user               string comment '扫描员',
    instore_early_scantime           string comment '入库时间',
    instore_early_scansitecode       string comment '入库网点',
    outstore_early_scantime          string comment '出库时间',
    outstore_early_scansitecode      string comment '出库网点',
    sign_early_scantime              string comment '签收时间',
    sign_early_scansitecode          string comment '签收网点',
    next_next_network_code           string comment '下下一站',
    next_next_code_type              string comment '下下一站类型',
    next_next_arrival_early_time     string comment '下下一站到件时间',
    remark2                          string comment '备用',
    remark3                          string comment '备用',
    remark4                          string comment '备用',
    remark5                          string comment '备用'

) COMMENT '错分下一站'
    PARTITIONED BY ( dt STRING COMMENT 't-1')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_tab_missrate_trace_waybill_next_dt'
;

